﻿-- =============================================
-- Author:		Dmitry Nazarov
-- Create date: 29.03.2011
-- Description:	Отчет Задолженность основных заказчиков
-- =============================================
CREATE PROCEDURE [dbo].[report_debt_principal_customers] 
	@p_date_from date, 
	@p_date_to date,
	@p_nsi_type_of_activity_id int,
	@p_nsi_organization_id int,
	@p_contract_id	int
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT	o.SHORT_NAME as ORGANIZATION_NAME,
			vi.NUMBER as VI_NUMBER,
			vi.[DATE] as VI_DATE,
			vi.INVOICE_OF_PAY_ADD_NUM as INVOICE_NUMBER,
			--NULL as INVOICE_DATE,
			pu.NAME as PRODUCT_NAME,
			cp.NUMBER as CUST_PROTOCOL_NUMBER,
			vi.GUARANTY_LET_DATE,
			dbo.f_convert_to_rur(SUM(pu.COST) - ISNULL(SUM(pu.PAYMENT_IN_SUM_CUR),0), [vi].DATE, cp.NSI_CURRENCY_ID) as REST_SUM,
			COUNT(*) as PRODUCT_COUNT					
	FROM VIEW_SEARCH_P_U_FOR_FORMS as pu
	INNER JOIN VAT_INVOICE vi ON vi.ID = pu.VAT_INVOICE_ID
	LEFT JOIN NSI_ORGANIZATION as o ON o.ID = vi.NSI_ORGANIZATION_ID
	--INNER JOIN INVOICE_OUT_ITEM as ioi ON [pu].ID = ioi.PRODUCT_UNIT_ID
	--INNER JOIN INVOICE_OUT as [io] ON [io].ID = ioi.INVOICE_OUT_ID
	LEFT JOIN PRODUCT_COST as pc ON pu.ID = pc.PRODUCT_UNIT_ID
	INNER JOIN COST_PROTOCOL as cp ON cp.ID = pc.COST_PROTOCOL_ID
	--LEFT JOIN PAYMENT_IN_ITEM pii ON pu.ID = pii.PRODUCT_UNIT_ID
	INNER JOIN PID ON PID.ID = [pu].PID
	INNER JOIN [CONTRACT] as c ON c.ID = PID.CONTR_ID
	where pu.PAY_PER < 100
	AND vi.IS_GUARANTY_LET = 1
	AND [vi].[DATE] BETWEEN @p_date_from AND @p_date_to
    AND
    (
		(@p_nsi_type_of_activity_id is null)
		OR
		(c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id)
    )
    AND
    (
		(@p_nsi_organization_id is null)
		OR
		(c.NSI_ORGANIZATION_ID = @p_nsi_organization_id)
    )
     AND
    (
		(@p_contract_id is null)
		OR
		(c.ID = @p_contract_id)
    )
	GROUP BY o.SHORT_NAME, vi.NUMBER, vi.[DATE], pu.NAME, cp.NUMBER, vi.GUARANTY_LET_DATE,
	pc.COST, vi.[DATE], cp.NSI_CURRENCY_ID, vi.INVOICE_OF_PAY_ADD_NUM

END